Snowflakeのセッション変数を使う

Snowflakeでセッション変数を設定しながら、SQLを発行する。それによりSQLのパラメーターを動的に変更する。

「カスタマーストーリー」を支えるツール特集

#Snowflake

#ビジネス・アナリティクス

#ビッグデータ

ikeda

2021.07.08

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部、池田です。
Snowflakeのセッション変数（SQL変数）を使っていろいろ試す機会があったので、ブログにします。
公式のドキュメントは↓
【 SQL 変数】

セッション変数関連のクエリ

こんな感じで使えます。

変数の設定
SET {変数名} = '{値}';
SET ({変数名1}, {変数名2}, {変数名3}) = ('{値1}', '{値2}', '{値3}');
変数の確認
SHOW VARIABLES;
変数の削除
UNSET {変数名};

呼び出す場合は $VAR_1 のようなかたちで「$」を使います。

名前の通りセッション内でのみ有効です。以前のブログでワークシート上の変数がいつ削除されるのか調べています。

Snowflakeのワークシートでのトランザクションと変数の挙動まとめ（小ネタ）

また、今回調べた限り、セッション外で宣言/初期化しておける変数は無さそうです。
（私は、変数とパラメーターと混同してしまっていたのですが、パラメーターはアカウント/ユーザー/セッションごとに設定ができます。例えば、ユーザー単位でタイムゾーン（ TIMEZONE ）パラメーターのデフォルト値を変えておいて、更にクエリの発行時にセッション内で再度タイムゾーンパラメーターを上書きする、ということも可能です。変数ではそういったことができなそうです。）

セッション変数を呼び出して使う

このように、SQLのパラメータに使うこともできます。

SET PREFIX_REGEX = (SELECT 'snowflake/input/' || TO_VARCHAR(CURRENT_DATE(), 'YYYYMMDD') || '/.*'); -- 変数の初期化

COPY INTO DEVIO_SAMPLE_TBL -- （例）作成済みステージ&テーブルを使ったロード
FROM @DEVIO_EXT_STAGE_S3
PATTERN = $PREFIX_REGEX -- 変数を使用
;

（1行目はSELECT文にしなくても、結合と関数だけでも使用できます。）

5行目の PATTERN で関数を直接使用できない（ PATTERN = TO_VARCHAR(CURRENT_DATE(), 'YYYYMMDD') のような書き方ができない）ので、こういうことがしたい場合は変数を使うことになりそうです。

また、テーブル名などに使う場合は、 IDENTIFIER() や TABLE() 関数を噛ませて識別子として認識させる必要があります。以前のブログで利用していました。例↓

-- ファイル形式名・テーブル名・ビュー名
SET (format_name, table_name, view_name) = ('{ファイル形式名}', '{テーブル名}', '{ビュー名}');

-- …（略）…

-- ビュー
CREATE VIEW IDENTIFIER($view_name)
AS
    SELECT
        V:id_str::VARCHAR AS "ID_STR",
        V:str::VARCHAR AS "STR",
        V:date::TIMESTAMP_NTZ AS "DATE",
        V:int::INT AS "INT",
        V:str_ja::VARCHAR AS "STR_JA"
    FROM TABLE($table_name)
    WHERE
        V:import_datetime = (
            SELECT MAX(V:import_datetime)
            FROM TABLE($table_name)
        )
;

タスクで変数は使えるの…？

実は私が最終的にやりたかったことは↓のようにタスクと組み合わせての利用です。

-- タスク（作成できない）
CREATE TASK DEVIO_DAILY_COPY_TASK
    WAREHOUSE = DEVIO_WH
    SCHEDULE = 'USING CRON 0 1 * * * Asia/Tokyo'
AS
    SET PREFIX_REGEX = (SELECT 'snowflake/input/' || TO_VARCHAR(CURRENT_DATE(), 'YYYYMMDD') || '/.*');
    COPY INTO PUBLIC.DEVIO_SAMPLE_TBL
    FROM @DEVIO_EXT_STAGE_S3
    PATTERN = $PREFIX_REGEX;
;

↑このSQLはエラーになります。

現在、タスクは、ストアドプロシージャの呼び出しを含む単一の SQL ステートメントを実行できます。

とのことなので、AS句の中に複数SQLを定義することはできないようです。
また、前述の通りセッション外（タスクの外）から変数を渡す方法も無さそうなので、こういうことがしたい場合は、ストアドプロシージャを使う必要がありそうです。（タスク上ではストアドプロシージャをCALLするだけ。）
その辺は今度ブログにします。たぶん。